package com.pervasa.demo.kitsample.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

enum CurrentMode
{
    MODE_PUSH,
    MODE_PULL
}

public class DataHistoryManager {

    private Connection conn = null;
    private int historyKey = 0;
    private int resultKey = 0;
    
    private CurrentMode cCurrentMode = CurrentMode.MODE_PUSH;
    private CurrentMode hCurrentMode = CurrentMode.MODE_PUSH;
    private CurrentMode tCurrentMode = CurrentMode.MODE_PUSH;
    private CurrentMode pCurrentMode = CurrentMode.MODE_PUSH;

    private int cSensorInterval = 0;
    private int hSensorInterval = 0;
    private int tSensorInterval = 0;
    private int pSensorInterval = 0;

    private Timestamp cTime = null;
    private Timestamp hTime = null;
    private Timestamp tTime = null;
    private Timestamp pTime = null;

    private Timestamp cPushStartTime = null;
    private Timestamp hPushStartTime = null;
    private Timestamp tPushStartTime = null;
    private Timestamp pPushStartTime = null;

    private String cSensorID = null;
    private String hSensorID = null;
    private String tSensorID = null;
    private String pSensorID = null;

    public DataHistoryManager()
    {
        Statement stmt = null;
        String sql = null;

        try
        {
            if(conn == null)
            {
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                conn =DriverManager.getConnection("jdbc:mysql://localhost", "root", "cnt5517");
            }

            stmt = conn.createStatement();

            sql = "CREATE DATABASE IF NOT EXISTS reactive_engine";
            stmt.executeUpdate(sql);

            sql = "USE reactive_engine";
            stmt.executeUpdate(sql);

            sql = "DROP TABLE IF EXISTS history";
            stmt.executeUpdate(sql);

            sql = "DROP TABLE IF EXISTS shortcut_result";
            stmt.executeUpdate(sql);

            sql = "CREATE TABLE history" +
            "(id INT(11), " +
            "timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
            "sensor_id VARCHAR(10), " +
            "sensor_value INT(11), " +
            "push_pull INT(11), " +
            "PRIMARY KEY (id))";
            stmt.executeUpdate(sql);

            sql = "CREATE TABLE shortcut_result" +
            "(id INT(11), " +
            "total_sensor INT(11), " +
            "pulling_sensor INT(11), " +
            "read_sensor INT(11), " +
            "PRIMARY KEY (id))";
            stmt.executeUpdate(sql);

            stmt.close();
            disconnect();
        }
        catch(SQLException ex)
        {
            System.out.println("Error : Could not execute DELETE query");
        }
        catch(Exception ex)
        {
            System.out.println("Error : Could not load JDBC driver");
        }
    }

    public void setMode(String sensorID, CurrentMode currentMode)
    {
    	if(sensorID == cSensorID)
    	{
            cCurrentMode = currentMode;

            if(currentMode == CurrentMode.MODE_PUSH)
            {
                cPushStartTime.setTime(new Timestamp(System.currentTimeMillis()).getTime());
            }
    	}
    	else if(sensorID == hSensorID)
    	{
            hCurrentMode = currentMode;

            if(currentMode == CurrentMode.MODE_PUSH)
            {
                hPushStartTime.setTime(new Timestamp(System.currentTimeMillis()).getTime());
            }
    	}
    	else if(sensorID == tSensorID)
    	{
            tCurrentMode = currentMode;

            if(currentMode == CurrentMode.MODE_PUSH)
            {
                tPushStartTime.setTime(new Timestamp(System.currentTimeMillis()).getTime());
            }
    	}
    	else if(sensorID == pSensorID)
    	{
            pCurrentMode = currentMode;

            if(currentMode == CurrentMode.MODE_PUSH)
            {
                pPushStartTime.setTime(new Timestamp(System.currentTimeMillis()).getTime());
            }
    	}
    	else
    	{
    	    System.out.println("Error : Invalid sensor ID : " + sensorID);
    	}
    }

    private boolean isPush(String sensorID)
    {
        boolean isPush = true;

        if(sensorID == cSensorID)
    	{
            isPush = cCurrentMode == CurrentMode.MODE_PUSH ? true : false;
    	}
    	else if(sensorID == hSensorID)
    	{
            isPush = hCurrentMode == CurrentMode.MODE_PUSH ? true : false;
    	}
    	else if(sensorID == tSensorID)
    	{
            isPush = tCurrentMode == CurrentMode.MODE_PUSH ? true : false;
    	}
    	else if(sensorID == pSensorID)
    	{
            isPush = pCurrentMode == CurrentMode.MODE_PUSH ? true : false;
    	}
    	else
    	{
    	    System.out.println("Error : Invalid sensor ID : " + sensorID);
    	}

    	return isPush;
    }

    private Timestamp getSensorPushStartTime(String sensorID)
    {
        Timestamp timestamp = null;

        if(sensorID == cSensorID)
    	{
            timestamp = cPushStartTime;
    	}
    	else if(sensorID == hSensorID)
    	{
            timestamp = hPushStartTime;
    	}
    	else if(sensorID == tSensorID)
    	{
            timestamp = tPushStartTime;
    	}
    	else if(sensorID == pSensorID)
    	{
            timestamp = pPushStartTime;
    	}
    	else
    	{
    	    System.out.println("Error : Invalid sensor ID : " + sensorID);
    	}

        return timestamp;
    }

    public void setContactSensorID(String id)
    {
        cSensorID = id;
        System.out.println("Contact sensor ID: " + id);
    }

    public String getContactSensorID()
    {
        return cSensorID;
    }

    public void setHumiditySensorID(String id)
    {
        hSensorID = id;
        System.out.println("Humidity sensor ID: " + id);
    }

    public String getHumiditySensorID()
    {
        return hSensorID;
    }

    public void setTemperatureSensorID(String id)
    {
        tSensorID = id;
        System.out.println("Temperature sensor ID: " + id);
    }

    public String getTemperatureSensorID()
    {
        return tSensorID;
    }

    public void setPressureSensorID(String id)
    {
        pSensorID = id;
        System.out.println("Pressure sensor ID: " + id);
    }

    public String getPressureSensorID()
    {
        return pSensorID;
    }

    private void connect()
    {
        try
        {
            if(conn == null)
            {
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                conn =DriverManager.getConnection("jdbc:mysql://localhost/reactive_engine", "root", "cnt5517");
            }
        }
        catch(SQLException ex)
        {
            System.out.println("Error : Could not connect to the database");
        }
        catch(Exception ex)
        {
            System.out.println("Error : Could not load JDBC driver");
        }
    }

    private void disconnect()
    {
        try
        {
            if(conn != null)
            {
                conn.close();
                conn = null;
            }
        }
        catch(SQLException ex)
        {
            System.out.println("Error : Could not close the connection");
        }
    }

    private synchronized ResultData requestQueryOperation(QueryParam param)
    {
        QueryOpType opType;
        ResultData resultData = null;
        Statement stmt = null;
        ResultSet rs = null;

        opType = param.getQueryOpType();

        try
        {
            if(opType == QueryOpType.OPTYPE_INSERT)
            {
                PreparedStatement pstmt = null;

                connect();

                pstmt = conn.prepareStatement("INSERT INTO history VALUES(?,?,?,?,?)");
                pstmt.setInt(1, historyKey++);
                pstmt.setTimestamp(2, param.getTimeStamp());
                pstmt.setString(3, param.getSensorID());
                pstmt.setInt(4, Integer.parseInt(param.getSensorValue()));
                pstmt.setInt(5, param.isPush() ? 1 : 0);
                pstmt.executeUpdate();

                pstmt.close();
                disconnect();
            }
            else if(opType == QueryOpType.OPTYPE_INSERT_RESULT)
            {
            	PreparedStatement pstmt = null;

                connect();

                pstmt = conn.prepareStatement("INSERT INTO shortcut_result VALUES(?,?,?,?)");
                pstmt.setInt(1, resultKey++);
                pstmt.setInt(2, param.getAllEventCount());
                pstmt.setInt(3, param.getPullEventCount());
                pstmt.setInt(4, param.getReadEventCount());
                pstmt.executeUpdate();

                pstmt.close();
                disconnect();
            }
            else 
            {
                connect();
                stmt = conn.createStatement();
                rs = stmt.executeQuery(param.getSQLString());

                if(rs.next())
                {
                    if(opType == QueryOpType.OPTYPE_GET_COUNT || opType == QueryOpType.OPTYPE_GET_INT)
                    {
                        resultData = new ResultData(rs.getInt(1));
                    }
                    else if(opType == QueryOpType.OPTYPE_GET_DOUBLE)
                    {
                        resultData = new ResultData(rs.getDouble(1));
                    }
                    else
                    {
                        System.out.println("Error : Invalid query operation type");
                    }
                }

                rs.close();
                disconnect();
            }
        }
        catch(SQLException ex)
        {
            System.out.println("Error : Could not execute query operation");
            ex.printStackTrace();
        }

        return resultData;
    }

    public void insertHistoryData(String sensorID, String sensorVal)
    {
        Timestamp currentTime = new Timestamp(System.currentTimeMillis());
        QueryParam param = new QueryParam();

        updateSensorInterval(sensorID, currentTime);

        param.setQueryOpType(QueryOpType.OPTYPE_INSERT);
        param.setSensorID(sensorID);
        param.setSensorValue(sensorVal);
        param.setPush(isPush(sensorID));
        param.setTimeStamp(currentTime);

        requestQueryOperation(param);
    }

    private void updateSensorInterval(String sensorID, Timestamp currentTime)
    {
        if(sensorID.equals(cSensorID)) // Contact sensor
        {
            if(cTime == null)
            {
                cTime = new Timestamp(currentTime.getTime());
                cPushStartTime = new Timestamp(currentTime.getTime());
            }
            else
            {
                cSensorInterval = (int)(currentTime.getTime() - cTime.getTime());
                cTime.setTime(currentTime.getTime());
            }
        }
        else if(sensorID.equals(pSensorID)) // Pressure sensor
        {
            if(pTime == null)
            {
                pTime = new Timestamp(currentTime.getTime());
                pPushStartTime = new Timestamp(currentTime.getTime());
            }
            else
            {
                pSensorInterval = (int)(currentTime.getTime() - pTime.getTime());
                pTime.setTime(currentTime.getTime());
            }
        }
        else if(sensorID.equals(hSensorID)) // Humidity sensor
        {
            if(hTime == null)
            {
                hTime = new Timestamp(currentTime.getTime());
                hPushStartTime = new Timestamp(currentTime.getTime());
            }
            else
            {
                hSensorInterval = (int)(currentTime.getTime() - hTime.getTime());
                hTime.setTime(currentTime.getTime());
            }
        }
        else if(sensorID.equals(tSensorID)) // Temperature
        {
            if(tTime == null)
            {
                tTime = new Timestamp(currentTime.getTime());
                tPushStartTime = new Timestamp(currentTime.getTime());
            }
            else
            {
                tSensorInterval = (int)(currentTime.getTime() - tTime.getTime());
                tTime.setTime(currentTime.getTime());
            }
        }
        else
        {
            System.out.println("Error : Invalid sensor ID");
        }
    }

    public int getSensorInterval(String sensorID)
    {
        int sensorInterval = 0;

        if(sensorID == cSensorID)
    	{
            sensorInterval = cSensorInterval;
    	}
    	else if(sensorID == hSensorID)
    	{
            sensorInterval = hSensorInterval;
    	}
    	else if(sensorID == tSensorID)
    	{
            sensorInterval = tSensorInterval;
    	}
    	else if(sensorID == pSensorID)
    	{
            sensorInterval = pSensorInterval;
    	}
    	else
    	{
    	    System.out.println("Error : Invalid sensor ID : " + sensorID);
    	}

    	return sensorInterval;
    }

    public double getProbability(TerminalEvent tEvent)
    {
        int totalNum = 0, hitNum = 0;
        String sql = null;

        QueryParam param = new QueryParam();
        param.setQueryOpType(QueryOpType.OPTYPE_GET_COUNT);

        sql = "SELECT COUNT(id) FROM history";
        param.setSQLString(sql);

        totalNum = requestQueryOperation(param).getCountValue();

        sql = "SELECT COUNT(id) FROM history WHERE sensor_id = '" + tEvent.getSensorId() + "' AND";

        if(tEvent.isRangeEvent() == true)
        {
            sql += " sensor_value <= " + tEvent.getEndValue() + " AND sensor_value >= " + tEvent.getStartValue();
        }
        else
        {
            sql += " sensor_value = " + tEvent.getEqualValue();
        }

        param.setSQLString(sql);
        hitNum = requestQueryOperation(param).getCountValue();

        return (double)hitNum / totalNum;
    }

    public void insertShortcutResult(int allEvent, int pullEvent, int readEvent)
    {
        QueryParam param = new QueryParam();

        param.setQueryOpType(QueryOpType.OPTYPE_INSERT_RESULT);
        param.setShortcutResult(allEvent, pullEvent, readEvent);

        requestQueryOperation(param);
    }

    public ResultData getRecentSensorValue(String sensorID)
    {
        String sql = null;

        QueryParam param = new QueryParam();
        param.setQueryOpType(QueryOpType.OPTYPE_GET_INT);

        sql = "SELECT sensor_value FROM history WHERE sensor_id = '" + sensorID + "' ORDER BY timestamp DESC";

        param.setSQLString(sql);
        return requestQueryOperation(param);
    }

    public ResultData getAverageSensorValue(String sensorID)
    {
        String sql = null;
        Timestamp timestamp = getSensorPushStartTime(sensorID);

        QueryParam param = new QueryParam();
        param.setQueryOpType(QueryOpType.OPTYPE_GET_DOUBLE);

        sql = "SELECT AVG(sensor_value) FROM history WHERE sensor_id = '" + sensorID + "' AND " +
              "timestamp >= '" + timestamp + "' AND push_pull = 1";

        param.setSQLString(sql);
        return requestQueryOperation(param);
    }

    public ResultData getStandardDeviation(String sensorID, int recentCount, boolean isPush)
    {
        String sql = null;
        Timestamp timestamp = getSensorPushStartTime(sensorID);

        QueryParam param = new QueryParam();
        param.setQueryOpType(QueryOpType.OPTYPE_GET_DOUBLE);

        sql = "SELECT STD(sensor_value) FROM (SELECT sensor_value FROM history WHERE sensor_id = '" + sensorID + "' AND " +
              "timestamp >= '" + timestamp + "' AND push_pull = " + (isPush?1:0) + " ORDER BY timestamp DESC LIMIT " + recentCount + ") aaa";

        param.setSQLString(sql);
        return requestQueryOperation(param);
    }

    public ResultData getRangeOfSensorValue(String sensorID)
    {
        int maxValue, minValue;
        String sql = null;
        Timestamp timestamp = getSensorPushStartTime(sensorID);

        QueryParam param = new QueryParam();
        param.setQueryOpType(QueryOpType.OPTYPE_GET_INT);

        sql = "SELECT MIN(sensor_value) FROM history WHERE sensor_id = '" + sensorID + "' AND " +
              "timestamp >= '" + timestamp + "' AND push_pull = 1";

        param.setSQLString(sql);
        minValue = requestQueryOperation(param).getResultSetValue();

        sql = "SELECT MAX(sensor_value) FROM history WHERE sensor_id = '" + sensorID + "' AND " +
              "timestamp >= '" + timestamp + "' AND push_pull = 1";

        param.setSQLString(sql);
        maxValue = requestQueryOperation(param).getResultSetValue();

        return new ResultData(minValue, maxValue);
    }

    public ResultData getRecentPushCount(String sensorID)
    {
        String sql = null;
        Timestamp timestamp = getSensorPushStartTime(sensorID);

        QueryParam param = new QueryParam();
        param.setQueryOpType(QueryOpType.OPTYPE_GET_COUNT);

        sql = "SELECT COUNT(id) FROM history WHERE sensor_id = '" + sensorID + "' AND " +
              "timestamp >= '" + timestamp + "' AND push_pull = 1";

        param.setSQLString(sql);

        return requestQueryOperation(param);
    }
}
